In certain prior art, a multi-row update (also called “array update”) of a table T in a relational database (such as Oracle) is specified by an array (A) of row-identifier(ID) and value(V) pairs. An example of such an update is to change in an Address table the area code from 703 to 571 in all rows wherein the zip is 22313. An SQL query for such an multi-row update, when presented to a database management system, is first parsed, followed by pre-execute processing (e.g. error checking to confirm table names and column names, code generation and optimization). Thereafter, the database management system enters a loop, for each ID and V pair in A to do the following:{Initialization( ); FindBlock(ID); FetchBlock( ); UpdateRow(V)}.In the above prior art loop, on each iteration, a single block B is individually identified as containing a value V to be updated by use of the identifier ID, by a function FindBlock. Function FindBlock typically uses a database index which may be, for example a B-Tree index or a hash index of the type well known in the art. Regardless of how the block is identified, the next step is to fetch the identified block B, which is performed by the function FetchBlock( ). Specifically, function FetchBlock looks up block B in memory (in an area called “buffer cache”), and if block B is not in memory, then a disk access is performed to bring block B into memory. Once the block B is in memory (in the buffer cache), then function FetchBlock returns block B. Thereafter, the value V is updated, in the function UpdateRow, and then the next iteration is performed.
During the next iteration, a next row that needs to be updated may or may not be contained in the same block B. If the next row to be updated is contained in a different block, such a different block is unlikely to be the very next block on the disk from which block B was read (unless all rows are being updated). Hence, prior art systems that automatically prefetch into the buffer cache, a block that is very next to block B that has been just read, are not effective in anticipating and servicing the needs of an array update operation.